<?php

// ini_set('display_errors',1);
// ini_set('display_startup_errors',1);
// error_reporting(-1);

if (!defined('BASEPATH'))
	exit('No direct script access allowed');

define("EMPTY", " - ");
class Report extends CI_Controller {

	//Doctrine EntityManager
	public $em;

	function __construct() {
		parent::__construct();

		//Not required if you autoload the library
		$this -> load -> library('doctrine');

		//Instantiate a Doctrine Entity Manager
		$this -> em = $this -> doctrine -> em;
		// $conn = $this->em->getConnection();
		//var_dump(models\service\CurrentUser::user());
		if (models\service\CurrentUser::user() == FALSE){
			header('location: ' . base_url());
		}
		
		$this->load->helper(array('dompdf', 'file'));
		// $conn->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'varchar');
	}
	
	
	// START Katalog
	public function index() {
		$user = models\service\CurrentUser::user();
		$criteria = array('userId' => $user->getUserId());
		$userProfiles = $this->em->getRepository('\models\entities\UserProfile')->findBy($criteria);
		
		$data['breadcrumb'] = array( array("label" => "Home", "link" => NULL),
								 array("label" => "Laporan Final", "link" => NULL)
								);
								
		$data['reports'] = $this->em->getRepository('\models\entities\Berkas')->paginateReportTrainingAbsensi();
		$criteria = array('userId' => $user->getUserId());
		$userProfiles = $this->em->getRepository('\models\entities\UserProfile')->findBy($criteria);
		$data['userProfiles'] = $userProfiles;
		$data['user'] = $user;
		$this -> load -> view('report/index', $data);		
	}

	function pdf(){
	     
		 
	     // page info here, db calls, etc.     
	     //$data = null;
	     $data['test'] = "test";
	     $html = $this->load->view('report/test', $data, true);
		 //var_dump($html);
		 //die('tests');
	     pdf_create($html, 'laporan');
	    /* or
	     $data = pdf_create($html, '', false);
	     write_file('name', $data); */
	     //if you want to write it to disk and/or send it as an attachment    
	}
	
	function rekapTrainingParticipant(){
		$user = models\service\CurrentUser::user();
		$criteria = array('userId' => $user->getUserId());
		$userProfiles = $this->em->getRepository('\models\entities\UserProfile')->findBy($criteria);
		$data['breadcrumb'] = array( array("label" => "Home", "link" => NULL),
								 array("label" => "Laporan Final", "link" => NULL)
								);
								
								
		$data['reports'] = $this->em->getRepository('\models\entities\Berkas')->paginateReportTrainingAbsensi();
		$criteria = array('userId' => $user->getUserId());
		$userProfiles = $this->em->getRepository('\models\entities\UserProfile')->findBy($criteria);
		$data['userProfiles'] = $userProfiles;
		$data['user'] = $user;
		$html = $this -> load -> view('report/rekapTrainingParticipant', $data, true);
		pdf_create($html, 'filename');
	}
	
	function rekapLaporan(){
		$user = models\service\CurrentUser::user();
		$criteria = array('userId' => $user->getUserId());
		$userProfiles = $this->em->getRepository('\models\entities\UserProfile')->findBy($criteria);
		$data['breadcrumb'] = array( array("label" => "Home", "link" => base_url("authService/goHome")),
								 array("label" => "Laporan Final", "link" => NULL)
								);
		$data['propinsis'] = $this->em->getRepository('\models\entities\Propinsi')->findAll(array('nama' => 'asc'));
		//echo "<pre>"; print_r($data['propinsis']); die();						
		$data['distriks'] = $this->em->getRepository('\models\entities\Distrik')->findAll();
		$data['berkasTypes'] = $this->em->getRepository('\models\entities\TipeBerkas')->findAll();
		
		// init parameter all 
		$par['distrik'] = "-1";
		$par['desa'] = "-1";
		$par['tipeBerkas'] = "-1";
		$data['reports'] = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportByDistrikDesaTipeBerkas($par);
		$criteria = array('userId' => $user->getUserId());
		$userProfiles = $this->em->getRepository('\models\entities\UserProfile')->findBy($criteria);
		$data['userProfiles'] = $userProfiles;
		$data['user'] = $user;
		$data['initProfile'] = $userProfiles[0]->getProfile()->getId();
		$this -> load -> view('report/index', $data);
		//$html = $this -> load -> view('report/rekapTrainingParticipant', $data, true);
		//pdf_create($html, 'filename');
	}

	public function findRekapLaporanByDistrikDesaTipeAndRincian($distrikId = "-1", $desaId = "-1", $tipeId = "-1", $rincian = "-1"){
		header('Content-type: application/json');
				
		if ($tipeId == "-1"){
			//$trainingList = $this -> em -> getRepository('\models\entities\JenisTraining') -> findAll();
			$par['distrik'] = $distrikId;
			$par['desa'] = $desaId;
			$par['tipeBerkas'] = $tipeId;
			$reports = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportByDistrikDesaTipeBerkas($par);
		} else {
			// beragam report
			if ($tipeId == "TRAINING"){
				$par['distrik'] = $distrikId;
				$par['desa'] = $desaId;
				$par['rincian'] = $rincian;
				$reports = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportTrainingAbsensiByDistrikDesaJenisTraining($par);
			} else {
				$reports = array();
			}			
		}
		if (count($reports) > 0 && is_array($reports)){
			$result = array();
			$i = 0;
			if ($tipeId == "TRAINING"){
				$result = $reports;
			} else {
				foreach ($reports as $row){
					$result[$i]->namaDistrik = $row['namaDistrik'];
					$result[$i]->namaDesa = $row['namaDesa'];
					$result[$i]->tipeBerkas = $row['tipeBerkas'];
					$result[$i]->jumlah = $row['jumlahBerkas'];
					$result[$i]->desaId = $row['desaId'];
					$result[$i]->distrikId = $row['distrikId'];
					$result[$i]->tipeBerkasId = $row['tipeBerkasId'];
					$i++;
				}	
			}
			
			$obj->result = 1;
			$obj->mapData = $result;
			echo json_encode($obj);	
		} else {
			//$trainingList=array();
			echo json_encode($reports);
		}
	}
/*
	public function findRekapLaporanByPropinsiDistrikDesaTipeAndRincian($propinsi = "-1", $distrikId = "-1", $desaId = "-1", $tipeId = "-1", $rincian = "-1"){
		header('Content-type: application/json');
				
		if ($tipeId == "-1"){
			//$trainingList = $this -> em -> getRepository('\models\entities\JenisTraining') -> findAll();
			$par['propinsi'] = $propinsi;
			$par['distrik'] = $distrikId;
			$par['desa'] = $desaId;
			$par['tipeBerkas'] = $tipeId;
			$reports = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportByPropinsiDistrikDesaTipeBerkas($par);
		} else {
			// beragam report
			if ($tipeId == "TRAINING"){
				$par['propinsi'] = $propinsi;
				$par['distrik'] = $distrikId;
				$par['desa'] = $desaId;
				$par['rincian'] = $rincian;
				$reports = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportTrainingAbsensiByPropinsiDistrikDesaJenisTraining($par);
			} else {
				$reports = array();
			}			
		}
		if (count($reports) > 0 && is_array($reports)){
			$result = array();
			$i = 0;
			if ($tipeId == "TRAINING"){
				$result = $reports;
			} else {
				foreach ($reports as $row){
					$result[$i]->namaDistrik = $row['namaDistrik'];
					$result[$i]->namaDesa = $row['namaDesa'];
					$result[$i]->tipeBerkas = $row['tipeBerkas'];
					$result[$i]->jumlah = $row['jumlahBerkas'];
					$result[$i]->desaId = $row['desaId'];
					$result[$i]->distrikId = $row['distrikId'];
					$result[$i]->tipeBerkasId = $row['tipeBerkasId'];
					$i++;
				}	
			}
			
			$obj->result = 1;
			$obj->mapData = $result;
			echo json_encode($obj);	
		} else {
			//$trainingList=array();
			$obj->result = 1;
			$obj->mapData = $reports;
			echo json_encode($obj);
		}
	} */
	
	public function findRekapLaporanByPropinsiDistrikDesaTipeAndRincian($propinsi = "-1", $distrikId = "-1", $desaId = "-1", $tipeId = "-1", $rincian = "-1"){
		header('Content-type: application/json');
		/*		
		if ($tipeId == "-1"){
			//$trainingList = $this -> em -> getRepository('\models\entities\JenisTraining') -> findAll();
			$par['propinsi'] = $propinsi;
			$par['distrik'] = $distrikId;
			$par['desa'] = $desaId;
			$par['tipeBerkas'] = $tipeId;
			$reports = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportByPropinsiDistrikDesaTipeBerkas($par);
		} else {
			// beragam report
			if ($tipeId == "TRAINING"){
				$par['propinsi'] = $propinsi;
				$par['distrik'] = $distrikId;
				$par['desa'] = $desaId;
				$par['rincian'] = $rincian;
				$reports = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportTrainingAbsensiByPropinsiDistrikDesaJenisTraining($par);
			} else {
				$reports = array();
			}			
		} */
		$obj = new stdClass;
		$par['propinsi'] = $propinsi;
		$par['distrik'] = $distrikId;
		$par['desa'] = $desaId;
		$par['tipeBerkas'] = $tipeId;
		$par['rincian'] = $rincian;
		$reports = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportByPropinsiDistrikDesaTipeBerkas($par);
		if (count($reports) > 0 && is_array($reports)){
			// $result = array();
			// $i = 0;
			// foreach ($reports as $row){
				// $result[$i]->namaDistrik = $row['namaDistrik'];
				// $result[$i]->namaDesa = $row['namaDesa'];
				// $result[$i]->tipeBerkas = $row['tipeBerkas'];
				// $result[$i]->jumlah = $row['jumlahBerkas'];
				// $result[$i]->desaId = $row['desaId'];
				// $result[$i]->distrikId = $row['distrikId'];
				// $result[$i]->tipeBerkasId = $row['tipeBerkasId'];
				// $i++;
			// }
			$result = json_decode(json_encode($reports));
			$obj->result = 1;
			$obj->mapData = $result;
			echo json_encode($obj);	
		} else {
			//$trainingList=array();
			$obj->result = 1;
			$obj->mapData = $reports;
			echo json_encode($obj);
		}
	} 	
	
	public function findLaporanDetilByDistrikDesaTipe($distrikId = "-1", $desaId = "-1", $tipeId = "-1"){
		header('Content-type: application/json');
				
		$par['distrik'] = $distrikId;
		$par['desa'] = $desaId;
		$par['tipeBerkas'] = $tipeId;
		$reports = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportDetilByDistrikDesaTipeBerkas($par);
		if (count($reports) > 0 && is_array($reports)){
			$result = $reports;
			$obj->result = 1;
			$obj->mapData = $result;
			echo json_encode($obj);	
		} else {
			//$trainingList=array();
			echo json_encode($reports);
		}
	}
	
	public function findLaporanDetilByPropinsiDistrikDesaTipe($propinsiId="-1", $distrikId = "-1", $desaId = "-1", $tipeId = "-1"){
		header('Content-type: application/json');
		
		$par['propinsi'] = $propinsiId;		
		$par['distrik'] = $distrikId;
		$par['desa'] = $desaId;
		$par['tipeBerkas'] = $tipeId;
		$reports = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportDetilByPropinsiDistrikDesaTipeBerkas($par);
		if (count($reports) > 0 && is_array($reports)){
			$result = $reports;
			$obj = new stdClass();
			$obj->result = 1;
			$obj->mapData = $result;
			echo json_encode($obj);	
		} else {
			//$trainingList=array();
			echo json_encode($reports);
		}
	}
	
	public function viewDetilLaporan($id){
		//$profile = $this -> em -> getRepository('\models\entities\Profile') -> findOneBy(array('id' => $selectedProfile));
		
		$data['breadcrumb'] = array( array("label" => "Home", "link" => NULL),
								     array("label" => "Laporan Final", "link" => NULL)
								);
								
		$data['id'] = $id;
		$serv = new models\service\BerkasService;
		if ($id != "0"){
			// init the data
			$berkas = $serv->getBerkasById(TRUE, $id);
			$berkasComment = NULL;
			$berkasTraining = $this -> em -> getRepository('\models\entities\BerkasTraining') -> findOneBy(array('id' => $berkas));
			$berkasLkpm = $this -> em -> getRepository('\models\entities\BerkasLkpm') -> findOneBy(array('id' => $berkas));
			$berkasLas = $this -> em -> getRepository('\models\entities\BerkasLas') -> findOneBy(array('id' => $berkas));
			$memberOfTraining = $this -> em -> getRepository('\models\entities\ParticipantOfTraining') -> findBy(array('berkas' => $berkas));
			$berkasComments = $this -> em -> getRepository('\models\entities\BerkasComment') -> findBy(array('berkas' => $berkas), array('createdDate' => 'ASC'));									
			$berkasAttachments = $this -> em -> getRepository('\models\entities\BerkasAttachment') -> findBy(array('berkas' => $berkas), array('createdDate' => 'ASC'));
			$berkasKontribusis = $this -> em -> getRepository('\models\entities\BerkasKontribusi') -> findBy(array('berkas' => $berkas), array('id' => 'ASC'));
			if ($berkasLkpm != null){
				$jenisKegiatanList = $this -> em -> getRepository('\models\entities\JenisKegiatan') -> findBy(array('kategori' => $berkasLkpm->getJenisKegiatan()->getKategori()));		
			} else {
				$jenisKegiatanList = null;
			}
			
			$data['perpusList'] = $serv->findAllPerpustakaan(array('nama' => 'asc'));
			$data['desaList'] = $serv->findAllDesa(array('nama' => 'asc'));
		} else {
			$berkas = NULL;
			$berkasComments = NULL;
			$berkasTraining = NULL;
			$berkasLkpm = NULL;
			$berkasLas = NULL;
			$memberOfTraining = NULL;
			$berkasAttachments = NULL;
			$berkasKontribusis = array();
			$berkasLkpm = NULL;
			$jenisKegiatanList = NULL;			
			$data['perpusList'] = NULL;
			$data['desaList'] = NULL;
		}
		//echo "<pre>";print_r($berkas->getInbox()->getProcedureStep()->getId());die(); 
		$user = models\service\CurrentUser::user();
		$criteria = array('userId' => $user->getUserId());
		$userProfiles = $this->em->getRepository('\models\entities\UserProfile')->findBy($criteria);
		$data['userProfiles'] = $userProfiles;
		$data['berkasAttachments'] = $berkasAttachments;
		$data['berkasKontribusis'] = $berkasKontribusis;
		$data['memberOfTraining'] = $memberOfTraining;
		$data['kategoriKegiatanList'] = $this -> em -> getRepository('\models\entities\KategoriKegiatan') -> findAll(); 
		$data['jenisTrainingList'] = $this -> em -> getRepository('\models\entities\JenisTraining') -> findByUserProfiles($userProfiles);
		$data['topikKegiatanList'] = $this -> em -> getRepository('\models\entities\TopikKegiatan') -> findAll();
		$data['tipeBerkasList'] = $this -> em -> getRepository('\models\entities\TipeBerkas') -> findBy(array('status'=> 1));
		$data['kontribusis'] = $this -> em -> getRepository('\models\entities\Kontribusi') -> findAll(array('id' => 'ASC'));
		$data['medias'] = $this -> em -> getRepository('\models\entities\Media') -> findAll(array('id' => 'ASC'));
		$data['kategoriAsistensis'] = $this -> em -> getRepository('\models\entities\KategoriAsistensi') -> findAll(array('id' => 'ASC'));
		$data['jenisKegiatanList'] = $jenisKegiatanList;
		$data['distrikList'] = $serv->findAllDistrik(array('nama' => 'asc'));
		$data['berkas'] = $berkas;
		$data['berkasTraining'] = $berkasTraining;
		$data['berkasLkpm'] = $berkasLkpm;
		$data['berkasLas'] = $berkasLas;
		$data['berkasComments'] = $berkasComments;
		
		$criteria = array("id" => $berkas);	
		$inbox = $this -> em -> getRepository('\models\entities\Inbox')->findOneBy($criteria);
		$isEnabledSubmitBtn = TRUE;
		if ($inbox!=null && $inbox->getProcedureStep()->getIsParalel() == 1){
			if ($inbox->getProcedureStep()->getVerifyByJumlah() != NULL && $inbox->getProcedureStep()->getVerifyByJumlah()>0){
				if ($inbox->getLastVer() == $user->getUserId()){
					$isEnabledSubmitBtn = FALSE;
				}
			}	
											
			if ($inbox->getProcedureStep()->getVerifyByJabatan() != NULL && $inbox->getProcedureStep()->getVerifyByJabatan() != $user->getJabatan()->getId()){
				$isEnabledSubmitBtn = FALSE;
			}
		}
		$data['isEnabledSubmitBtn'] = $isEnabledSubmitBtn;
		$data['user'] = $user;
		$this->load->view('report/viewLaporan', $data);
	}


	public function cetakLaporanPDF($id=""){
		$berkasTraining = NULL;
		$berkasLkpm = NULL;
		$berkasLas = NULL;
		$memberOfTraining = array();	
		if ($id != ""){
			//$berkas = $serv->getBerkasById(TRUE, $id);
			$criteria = array('id' => $id);
			$berkas = $this -> em -> getRepository('\models\entities\Berkas') -> findByOne(TRUE, $criteria);
			
			if ($berkas->getTipe()->getId() == "TRAINING"){
				$berkasTraining = $this -> em -> getRepository('\models\entities\BerkasTraining') -> findOneBy(array('id' => $berkas));
				$memberOfTraining = $this -> em -> getRepository('\models\entities\ParticipantOfTraining') -> findBy(array('berkas' => $berkas));	
			} else if ($berkas->getTipe()->getId()=="LKPM"){
				$berkasLkpm = $this -> em -> getRepository('\models\entities\BerkasLkpm') -> findOneBy(array('id' => $berkas));
			} else if ($berkas->getTipe()->getId()=="LAS"){
				$berkasLas = $this -> em -> getRepository('\models\entities\BerkasLas') -> findOneBy(array('id' => $berkas));
			}
			$berkasAttachments = $this -> em -> getRepository('\models\entities\BerkasAttachment') -> findBy(array('berkas' => $berkas), array('createdDate' => 'ASC'));
			$berkasTerkirim  = 	$this -> em -> getRepository('\models\entities\BerkasActivity') -> findBerkasTerkirimById($berkas->getId());
			$berkasTereview  = 	$this -> em -> getRepository('\models\entities\BerkasActivity') -> findBerkasTereviewById($berkas->getId());
		}
		$data['berkas'] = $berkas;
		$data['berkasTraining'] = $berkasTraining;
		if (count($berkasTerkirim) >0){
			$data['berkasTerkirim'] = $berkasTerkirim[0];	
		} else {
			$data['berkasTerkirim'] = NULL;
		}
		if (count($berkasTereview) >0){
			$data['berkasTereview'] = $berkasTerkirim[0];	
		} else {
			$data['berkasTereview'] = NULL;
		}
		
		$data['berkasLkpm'] = $berkasLkpm;
		$data['berkasLas'] = $berkasLas;
		$data['memberOfTraining'] = $memberOfTraining; 
		$data['berkasAttachments'] = $berkasAttachments;
		//$this->load->view('report/cetakLaporan1', $data);
		$html = $this->load->view('report/finalReportPDF', $data, true);
		pdf_create($html, 'laporan');
	}
	
	public function finalReportHtml($id){
		$berkasTraining = NULL;
		$berkasLkpm = NULL;
		$berkasLas = NULL;
		$memberOfTraining = array();	
		if ($id != ""){
			//$berkas = $serv->getBerkasById(TRUE, $id);
			$criteria = array('id' => $id);
			$berkas = $this -> em -> getRepository('\models\entities\Berkas') -> findByOne(TRUE, $criteria);
			
			if ($berkas->getTipe()->getId() == "TRAINING"){
				$berkasTraining = $this -> em -> getRepository('\models\entities\BerkasTraining') -> findOneBy(array('id' => $berkas));
				$memberOfTraining = $this -> em -> getRepository('\models\entities\ParticipantOfTraining') -> findBy(array('berkas' => $berkas));
					
			} else if ($berkas->getTipe()->getId()=="LKPM"){
				$berkasLkpm = $this -> em -> getRepository('\models\entities\BerkasLkpm') -> findOneBy(array('id' => $berkas));
				
			} else if ($berkas->getTipe()->getId()=="LAS"){
				$berkasLas = $this -> em -> getRepository('\models\entities\BerkasLas') -> findOneBy(array('id' => $berkas));
			}
			$berkasAttachments = $this -> em -> getRepository('\models\entities\BerkasAttachment') -> findBy(array('berkas' => $berkas), array('createdDate' => 'ASC'));
			$berkasTerkirim  = 	$this -> em -> getRepository('\models\entities\BerkasActivity') -> findBerkasTerkirimById($berkas->getId());
			$berkasTereview  = 	$this -> em -> getRepository('\models\entities\BerkasActivity') -> findBerkasTereviewById($berkas->getId());
		}
		$data['berkas'] = $berkas;
		$data['berkasTraining'] = $berkasTraining;
		if (count($berkasTerkirim) >0){
			$data['berkasTerkirim'] = $berkasTerkirim[0];	
		} else {
			$data['berkasTerkirim'] = NULL;
		}
		if (count($berkasTereview) >0){
			$data['berkasTereview'] = $berkasTerkirim[0];	
		} else {
			$data['berkasTereview'] = NULL;
		}
		
		// laporan perpuseru
		$lastBerkasComment = $this->em->getRepository('\models\entities\BerkasComment')->findLastBerkasComment($id);
		if (count($lastBerkasComment) > 0) {
			$data['catatanPerpuseru'] = $lastBerkasComment[0]['text'];
		} else {
			$data['catatanPerpuseru'] = "";
		}
		$data['berkasLkpm'] = $berkasLkpm;
		$data['berkasLas'] = $berkasLas;
		$data['memberOfTraining'] = $memberOfTraining; 
		$data['berkasAttachments'] = $berkasAttachments;
		$this->load->view('report/finalReport1', $data);
	}
	
	public function rekapDistrik()
	{
		$serv = new models\service\AdministrationService;
		$indexAndCount[0] = 0;
		$indexAndCount[1] = 10;
		$data['distriks'] = $this->em->getRepository('\models\entities\Distrik')->findAll();
		
		$this->load->view('report/rekapDistrik',$data);
	}
	
	function exportExcelAll(){
		$this->load->library('PHPExcel');
		
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->getProperties()->setCreator("Perpuseru")
									 ->setTitle("Rekapitulasi Laporan Final")
									 ->setSubject("Rekapitulasi Laporan Final");
		$border = new PHPExcel_Style();
		$border->applyFromArray(
			array('borders' => array( 	'left' 		=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
										'top' 		=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
										'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
										'right'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN))));
		$objPHPExcel->setActiveSheetIndex(0)
		            ->setCellValue('A1', 'REKAPITULASI LAPORAN FINAL')
					->setCellValue('A2', ' ')
					->setCellValue('A3', 'Propinsi : ALL')
		            ->setCellValue('A4', 'Kab/Kota : ALL')
		            ->setCellValue('A5', 'Desa : ALL')
		            ->setCellValue('A6', 'Jenis Laporan : ALL')
		            ->setCellValue('A7', 'Rincian Laporan : ALL')
					->setCellValue('A8', 'Tanggal Export : ' . date("dmY H:i:s"));
		
		// set table header : namaPeserta
		$objPHPExcel->getActiveSheet()->setCellValue('B10', 'NO');
		$objPHPExcel->getActiveSheet()->setCellValue('C10', 'NAMA KAB/KOTA');
		$objPHPExcel->getActiveSheet()->setCellValue('D10', 'NAMA DESA');
		$objPHPExcel->getActiveSheet()->setCellValue('E10', 'JENIS LAPORAN');
		$objPHPExcel->getActiveSheet()->setCellValue('F10', 'RINCIAN LAPORAN');
		$objPHPExcel->getActiveSheet()->setCellValue('G10', 'JUMLAH LAPORAN');
		
		
		$reports = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportByDistrikDesaTipeBerkas(
				array("distrik" => "-1", "desa" => "-1", "tipeBerkas" => "-1")
			);
		// set row data sheet 1
		$i1 = 1;
		$row1 = 11;
		foreach ($reports as $value) {
			$rincianLaporan = "";
			$topikKegiatan = "N/A";
			//print_r($value);
			if ($value['tipeBerkasId'] == "TRAINING"){
				$rincianLaporan = $value['rincianTraining'];
			} else if ($value['tipeBerkasId'] == "LKPM"){
				$rincianLaporan = $value['rincianLkpm'];				
			} else if ($value['tipeBerkasId'] == "LAS"){
				$rincianLaporan = $value['rincianAsistensi'];
			}
			$objPHPExcel->getActiveSheet()->setCellValue('B'.$row1, $i1);
			$objPHPExcel->getActiveSheet()->setCellValue('C'.$row1, $value['namaDistrik']);
			$objPHPExcel->getActiveSheet()->setCellValue('D'.$row1, $value['namaDesa']);
			$objPHPExcel->getActiveSheet()->setCellValue('E'.$row1, $value['tipeBerkas']);
			$objPHPExcel->getActiveSheet()->setCellValue('F'.$row1, $rincianLaporan);
			$objPHPExcel->getActiveSheet()->setCellValue('G'.$row1, $value['jumlahBerkas']);
			$i1++;
			$row1++;
		}
		$objPHPExcel->getActiveSheet()->setSharedStyle($border, "B10:G".$row1);
		$objPHPExcel->getActiveSheet()->setTitle('Rekapitulasi');
		
		
		$objPHPExcel->getActiveSheet()->setSharedStyle($border, "B10:G".$i1);
		
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('V')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setAutoSize(true);
		$clonedSheet1 = clone $objPHPExcel->getActiveSheet();
		// set table header : rincian
		$clonedSheet1
			->setCellValue('B10', 'NO')
			->setCellValue('C10', 'KODE LAPORAN')
			->setCellValue('D10', 'NAMA KAB/KOTA')
			->setCellValue('E10', 'NAMA DESA')
			->setCellValue('F10', 'JENIS LAPORAN')
			->setCellValue('G10', 'RINCIAN LAPORAN / KATEGORI KEGIATAN / JENIS MENTORING')
			->setCellValue('H10', 'JENIS KEGIATAN / KATEGORI ASISTENSI')
			->setCellValue('I10', 'TOPIK KEGIATAN / MEDIA MONITORING')
			->setCellValue('J10', 'SASARAN')
			->setCellValue('K10', 'Tanggal Mulai Kegiatan')
			->setCellValue('L10', 'Tanggal Selesai Kegiatan')
			->setCellValue('M10', 'Jumlah Staff Perpustakaan')
			->setCellValue('M11', 'Laki - Laki')
			->setCellValue('N11', 'Perempuan')
			->setCellValue('O10', 'Jumlah Non Staff Perpustakaan')
			->setCellValue('O11', 'Laki - Laki')
			->setCellValue('P11', 'Perempuan')
			->setCellValue('Q10', 'Jumlah Lainnya')
			->setCellValue('Q11', 'Laki - Laki')
			->setCellValue('R11', 'Perempuan')
			->setCellValue('S10', 'Total Peserta')
			->setCellValue('S11', 'Laki - Laki')
			->setCellValue('T11', 'Perempuan')
			->setCellValue('U11', 'Keseluruhan')
			->setCellValue('V10', 'Tanggal Laporan Dibuat')
			->setCellValue('W10', 'Tanggal Laporan Disetujui');
		 
		$clonedSheet1
			->mergeCells('B10:B11')->mergeCells('C10:C11')
			->mergeCells('D10:D11')->mergeCells('E10:E11')
			->mergeCells('F10:F11')->mergeCells('G10:G11')
			->mergeCells('H10:H11')->mergeCells('I10:I11')
			->mergeCells('J10:J11')
			->mergeCells('K10:K11')->mergeCells('L10:L11')
			->mergeCells('M10:N10')
			->mergeCells('O10:P10')
			->mergeCells('Q10:R10')
			->mergeCells('S10:U10')
			->mergeCells('V10:V11')->mergeCells('W10:W11')
			;
		
		
		
		$searchBar['pFieldName'][0] = "Berkas.result";
		$searchBar['pCondition'][0] = "EQUAL";
		$searchBar['pFieldValue'][0] = "FINISH";
		$orders = array(
						array('fieldName' => 'Berkas.updateDate', 'orderType' => 'DESC')
					   );	
		$reports2 = $this -> em -> getRepository('\models\entities\Berkas')->paginateBerkasByMap2($searchBar, NULL, $orders);
		$numRow = count($reports2['data']) + 11;
		$clonedSheet1->setSharedStyle($border, "B10:W".$numRow);
		// set row data sheet 2
		$i1 = 1;
		$row1 = 12;
		foreach ($reports2['data'] as $value) {
			$clonedSheet1->setCellValue('B'.$row1, $i1);
			$clonedSheet1->setCellValue('C'.$row1, $value->getId());
			$clonedSheet1->setCellValue('D'.$row1, $value->getPerpustakaan()->getDesa()->getDistrik()->getNama());
			$clonedSheet1->setCellValue('E'.$row1, $value->getPerpustakaan()->getDesa()->getNama());
			$jenisLaporan = $value->getTipe()->getNama();
			$rincianLaporan = "";
			$jenisKegiatan = "N/A";
			$topikKegiatan = "N/A";
			$sasaran = "N/A";
			$fromDate = "";
			$toDate = "";
			$jmlhStaffP = "N/A";
			$jmlhStaffW = "N/A";
			$jmlhNonSP = "N/A";
			$jmlhNonSW = "N/A";
			$jmlhLainnyaP = "N/A";
			$jmlhLainnyaW = "N/A";
			$jmlhP = "N/A";
			$jmlhW = "N/A";
			$jmlhT = "N/A";
			if ($value->getTipe()->getId() == "TRAINING"){
				if (strtoupper($value->getPerpustakaan()->getNama()) == "PERPUSERU" || $value->getPerpustakaan()->getId() == "P2.111"){
					$jenisLaporan = "Kegiatan Perpuseru";
				}
				$rincianLaporan = $value->getBerkasTraining()->getJenisTraining()->getNama();
				// $fromDate = $value->getBerkasTraining()->getFromDate()->format('d-m-Y');
				// $toDate = $value->getBerkasTraining()->getToDate()->format('d-m-Y');
				$fromDate = '-';
				$toDate = '-';
				if ($value->getBerkasTraining()->getFromDate() <> null) {
					$fromDate = $value->getBerkasTraining()->getFromDate()->format('d-m-Y');
				}
				
				if ($value->getBerkasTraining()->getFromDate() <> null) {
					$toDate = $value->getBerkasTraining()->getToDate()->format('d-m-Y');
				}
				
				$criteria = array('berkas' => $value);
				$participantOfTrainings = $this->em->getRepository('\models\entities\ParticipantOfTraining')->findBy($criteria);
				$jmlhStaffP = 0;
				$jmlhStaffW = 0;
				$jmlhNonSP = 0;
				$jmlhNonSW = 0;
				$jmlhP = 0;
				$jmlhW = 0;
				$jmlhLainnyaP = 0;
				$jmlhLainnyaW = 0;
				foreach ($participantOfTrainings as $peserta){
					if ($peserta->getStaff() == "S" && $peserta->getGender() == "M"){
						$jmlhStaffP++;
						$jmlhP++;
					} else if ($peserta->getStaff() == "S" && $peserta->getGender() == "F"){
						$jmlhStaffW++;
						$jmlhW++;
					} else if ($peserta->getStaff() == "N" && $peserta->getGender() == "M"){
						$jmlhNonSP++;
						$jmlhP++;
					} else if ($peserta->getStaff() == "N" && $peserta->getGender() == "F"){
						$jmlhNonSW++;
						$jmlhW++;
					} else if ($peserta->getStaff()== "L" && $peserta->getGender() == "M"){
						$jmlhLainnyaP++;
						$jmlhP++;
					} else if ($peserta->getStaff()== "L" && $peserta->getGender() == "F"){
						$jmlhLainnyaW++;
						$jmlhW++;
					} 
				}
				$jmlhT = $jmlhP + $jmlhW;
			} else if ($value->getTipe()->getId() == "LKPM"){
				
				$jenisKegiatan = $value->getBerkasLkpm()->getJenisKegiatan()->getNama();
				$rincianLaporan = $value->getBerkasLkpm()->getJenisKegiatan()->getKategori()->getNama();
				$fromDate = '-';
				$toDate = '-';
				if ($value->getBerkasLkpm()->getFromDate() <> null) {
					$fromDate = $value->getBerkasLkpm()->getFromDate()->format('d-m-Y');
				}
				
				if ($value->getBerkasLkpm()->getFromDate() <> null) {
					$toDate = $value->getBerkasLkpm()->getToDate()->format('d-m-Y');
				}
				if ($value->getBerkasLkpm()->getTopikKegiatan() != NULL){
					$topikKegiatan = $value->getBerkasLkpm()->getTopikKegiatan()->getNama();
				}
				$criteria = array('berkas' => $value);
				$berkasLkpmSasarans = $this->em->getRepository('\models\entities\BerkasLkpmSasaran')->findBy($criteria);
				$arrSasarans = array();
					foreach ($berkasLkpmSasarans as $berkasLkpmSasaran){
						if ($berkasLkpmSasaran->getInstitusiLain() != null ){
							$arrSasarans[] = $berkasLkpmSasaran->getKelompokSasaran()->getNama() . "(".$berkasLkpmSasaran->getInstitusiLain().")";
						} else {
							$arrSasarans[] = $berkasLkpmSasaran->getKelompokSasaran()->getNama();
						}
					}
				$sasaran = implode(",", $arrSasarans);	
				
				$jmlhP = $value->getBerkasLkpm()->getJumlahPria();
				$jmlhW = $value->getBerkasLkpm()->getJumlahWanita();
				$jmlhT = $jmlhP + $jmlhW;
			} else if ($value->getTipe()->getId() == "LAS"){
				//$rincianLaporan = $value->getBerkasLas()->getKategoriAsistensi()->getNama();
				if ($value->getBerkasLas()->getJenisAsistensi() == 1){
					$rincianLaporan = "NON KUNJUNGAN";
				} else {
					$rincianLaporan = "KUNJUNGAN";
				}
				
				$jenisKegiatan = $value->getBerkasLas()->getKategoriAsistensi()->getNama();
				$topikKegiatan = $value->getBerkasLas()->getMedia()->getNama();
				// $fromDate = $value->getBerkasLas()->getFromDate()->format('d-m-Y');
				// $toDate = $value->getBerkasLas()->getToDate()->format('d-m-Y');
				$fromDate = '-';
				$toDate = '-';
				if ($value->getBerkasLas()->getFromDate() <> null) {
					$fromDate = $value->getBerkasLas()->getFromDate()->format('d-m-Y');
				}
				
				if ($value->getBerkasLas()->getFromDate() <> null) {
					$toDate = $value->getBerkasLas()->getToDate()->format('d-m-Y');
				}
			}
			$clonedSheet1->setCellValue('F'.$row1, $jenisLaporan);
			$clonedSheet1->setCellValue('G'.$row1, $rincianLaporan);
			$clonedSheet1->setCellValue('H'.$row1, $jenisKegiatan); 
			$clonedSheet1->setCellValue('I'.$row1, $topikKegiatan);
			$clonedSheet1->setCellValue('J'.$row1, $sasaran);
			$clonedSheet1->setCellValue('K'.$row1, $fromDate); 
			$clonedSheet1->setCellValue('L'.$row1, $toDate); 
			$clonedSheet1->setCellValue('M'.$row1, $jmlhStaffP);
			$clonedSheet1->setCellValue('N'.$row1, $jmlhStaffW); 
			$clonedSheet1->setCellValue('O'.$row1, $jmlhNonSP);
			$clonedSheet1->setCellValue('P'.$row1, $jmlhNonSW);
			$clonedSheet1->setCellValue('Q'.$row1, $jmlhLainnyaP);
			$clonedSheet1->setCellValue('R'.$row1, $jmlhLainnyaW);
			$clonedSheet1->setCellValue('S'.$row1, $jmlhP);
			$clonedSheet1->setCellValue('T'.$row1, $jmlhW);
			$clonedSheet1->setCellValue('U'.$row1, $jmlhT);
			$clonedSheet1->setCellValue('V'.$row1, $value->getStartDate()->format('d-m-Y'));
			$clonedSheet1->setCellValue('W'.$row1, $value->getEndDate()->format('d-m-Y'));
			$i1++;
			$row1++;
		}
		
		$clonedSheet1->setTitle('Rincian');
		$objPHPExcel->addSheet($clonedSheet1);
		
		//$clonedSheet2 = clone $objPHPExcel->getActiveSheet();
		$clonedSheet2 = $objPHPExcel->createSheet();
		//$objPHPExcel->setActiveSheetIndex(0);
		
		$clonedSheet2->setTitle('Nama Peserta Pelatihan');
		// set table header : namaPeserta
		$clonedSheet2->getColumnDimension('B')->setAutoSize(true);
		$clonedSheet2->getColumnDimension('C')->setAutoSize(true);
		$clonedSheet2->getColumnDimension('D')->setAutoSize(true);
		$clonedSheet2->getColumnDimension('E')->setAutoSize(true);
		$clonedSheet2->setCellValue('A1', 'REKAPITULASI LAPORAN FINAL')
					->setCellValue('A2', ' ')
					->setCellValue('A3', 'Propinsi : ALL')
		            ->setCellValue('A4', 'Kab/Kota : ALL')
		            ->setCellValue('A5', 'Desa : ALL')
		            ->setCellValue('A6', 'Jenis Laporan : ALL')
		            ->setCellValue('A7', 'Rincian Laporan : ALL')
		            ->setCellValue('A8', 'Tanggal Export : ' . date("dmY H:i:s"));
		$clonedSheet2
			->setCellValue('B10', 'KODE LAPORAN')
			->setCellValue('C10', 'NAMA PESERTA')
			->setCellValue('D10', 'JENIS KELAMIN')
			->setCellValue('E10', 'STAF/NON STAF/LAINNYA')
			
			;
		
		
		
		// set row data sheet 3
		$i1 = 1;
		$row1 = 11;
		foreach ($reports2['data'] as $value) {
			if ($value->getTipe()->getId()=="TRAINING"){
				$criteria = array('berkas' => $value);
			    $participantOfTrainings = $this->em->getRepository('\models\entities\ParticipantOfTraining')->findBy($criteria);
				foreach ($participantOfTrainings as $peserta){
					$clonedSheet2->setCellValue('B'.$row1, $value->getId());
					$clonedSheet2->setCellValue('C'.$row1, $peserta->getName());
					$gender = ($peserta->getGender() == 'M' ? 'Laki-Laki' : 'Perempuan');
					$staff = "Lainnya";
					if ($peserta->getStaff() == 'S'){
						$staff = "Staff";
					} else if ($peserta->getStaff() == 'N'){
						$staff = "Non Staff";
					}
					$clonedSheet2->setCellValue('D'.$row1, $gender);
					$clonedSheet2->setCellValue('E'.$row1, $staff);
					$i1++;
					$row1++;
				}		
			}
		}
		$clonedSheet2->setSharedStyle($border, "B10:E".$row1);
		
		//$objPHPExcel->addSheet($clonedSheet2);
		
		$objPHPExcel->setActiveSheetIndex(0);
		
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		//header('Content-Disposition: attachment;filename="'."rekapitulasi_" . date("Y_m_d") . '.xls'.'"');
		header('Content-Disposition: attachment;filename="'."rekapitulasi_" . date("Y_m_d").".xlsx");
		header('Cache-Control: max-age=0');
		// If you're serving to IE 9, then the following may be needed
		header('Cache-Control: max-age=1');
		
		// If you're serving to IE over SSL, then the following may be needed
		header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
		header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
		header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
		header ('Pragma: public'); // HTTP/1.0
		
		$objWriter->save('php://output');
	}
	
	function exportExcelByFilter(){
		$this->load->library('PHPExcel');
		// echo "<pre>";print_r($_POST);die();
		$propinsi = ($this->input->post("Fpropinsi") != "-1") ? $this->em->getRepository('\models\entities\Propinsi')->findOneBy(array('id' => $this->input->post("Fpropinsi")))->getNama() : "INDONESIA";
		$distrik = ($this->input->post("Fdistrik") != "-1") ? $this->em->getRepository('\models\entities\Distrik')->findOneBy(array('id' => $this->input->post("Fdistrik")))->getNama() : "-";
		$desa = ($this->input->post("Fdesa") != "-1") ? $this->em->getRepository('\models\entities\Desa')->findOneBy(array('id' => $this->input->post("Fdesa")))->getNama() : "-";
		$tipe_berkas = ($this->input->post("FtipeBerkas") != "-1") ? $this->em->getRepository('\models\entities\TipeBerkas')->findOneBy(array('id' => $this->input->post("FtipeBerkas")))->getNama() : "-";
		
		$rincian_laporan = '-';
			if ($this->input->post("FtipeBerkas") == "TRAINING"){
				$rincian_laporan = ($this->input->post("FrincianLaporan") != "-1") ? $this->em->getRepository('\models\entities\JenisTraining')->findOneBy(array('id' => $this->input->post("FrincianLaporan")))->getNama() : "-";
			} else if ($this->input->post("FtipeBerkas") == "LKPM"){
				$rincian_laporan = ($this->input->post("FrincianLaporan") != "-1") ? $this->em->getRepository('\models\entities\JenisKegiatan')->findOneBy(array('id' => $this->input->post("FrincianLaporan")))->getNama() : "-";
			} else if ($this->input->post("FtipeBerkas") == "LAS"){
				$rincian_laporan = ($this->input->post("FrincianLaporan") != "-1") ? $this->em->getRepository('\models\entities\KategoriAsistensi')->findOneBy(array('id' => $this->input->post("FrincianLaporan")))->getNama() : "-";
			}
		
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->getProperties()->setCreator("Perpuseru")
									 ->setTitle("Rekapitulasi Laporan Final")
									 ->setSubject("Rekapitulasi Laporan Final");

		$objPHPExcel->setActiveSheetIndex(0)
		            ->setCellValue('A1', "REKAPITULASI LAPORAN FINAL - PROPINSI " . $propinsi)
					->setCellValue('A2', ' ')
					->setCellValue('A3', 'Propinsi : ' . $propinsi)
		            ->setCellValue('A4', 'Kab/Kota : ' . $distrik)
		            ->setCellValue('A5', 'Desa : ' . $desa)
		            ->setCellValue('A6', 'Jenis Laporan : ' . $tipe_berkas)
		            ->setCellValue('A7', 'Rincian Laporan : ' . $rincian_laporan)
		            ->setCellValue('A8', 'Tanggal Export : ' . date("dmY H:i:s"));
		
		// set table header : namaPeserta
		$objPHPExcel->getActiveSheet()->setCellValue('B10', 'NO');
		$objPHPExcel->getActiveSheet()->setCellValue('C10', 'NAMA KAB/KOTA');
		$objPHPExcel->getActiveSheet()->setCellValue('D10', 'NAMA DESA');
		$objPHPExcel->getActiveSheet()->setCellValue('E10', 'JENIS LAPORAN');
		$objPHPExcel->getActiveSheet()->setCellValue('F10', 'RINCIAN LAPORAN');
		$objPHPExcel->getActiveSheet()->setCellValue('G10', 'JUMLAH LAPORAN');
		
		// $reports = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportByDistrikDesaTipeBerkas(
				// array("distrik" => $this->input->post("Fdistrik"), "desa" => $this->input->post("Fdesa"), "tipeBerkas" => $this->input->post("FtipeBerkas"))
			// );
		
		$reports = $this -> em -> getRepository('\models\entities\Berkas')->paginateReportByPropinsiDistrikDesaTipeBerkas(
				array("propinsi" => $this->input->post("Fpropinsi"), "distrik" => $this->input->post("Fdistrik"), "desa" => $this->input->post("Fdesa"), "tipeBerkas" => $this->input->post("FtipeBerkas"), "rincian" => $this->input->post("FrincianLaporan"))
			);
			
		// set row data sheet 1
		$i1 = 1;
		$row1 = 11;
		foreach ($reports as $value) {
			$rincianLaporan = "";
			//print_r($value);
			if ($value['tipeBerkasId'] == "TRAINING"){
				$rincianLaporan = $value['rincianTraining'];
			} else if ($value['tipeBerkasId'] == "LKPM"){
				$rincianLaporan = $value['rincianLkpm'];
			} else if ($value['tipeBerkasId'] == "LAS"){
				$rincianLaporan = $value['rincianAsistensi'];
			}
			$objPHPExcel->getActiveSheet()->setCellValue('B'.$row1, $i1);
			$objPHPExcel->getActiveSheet()->setCellValue('C'.$row1, $value['namaDistrik']);
			$objPHPExcel->getActiveSheet()->setCellValue('D'.$row1, $value['namaDesa']);
			$objPHPExcel->getActiveSheet()->setCellValue('E'.$row1, $value['tipeBerkas']);
			$objPHPExcel->getActiveSheet()->setCellValue('F'.$row1, $rincianLaporan);
			$objPHPExcel->getActiveSheet()->setCellValue('G'.$row1, $value['jumlahBerkas']);
			$i1++;
			$row1++;
		}
		
		$objPHPExcel->getActiveSheet()->setTitle('Rekapitulasi');
		
		$border = new PHPExcel_Style();
		$border->applyFromArray(
			array('borders' => array( 	'left' 		=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
										'top' 		=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
										'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
										'right'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN))));
		
		$objPHPExcel->getActiveSheet()->setSharedStyle($border, "B10:G".(9+$i1));
		$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('T')->setAutoSize(true); 
		$objPHPExcel->getActiveSheet()->getColumnDimension('U')->setAutoSize(true);
		$objPHPExcel->getActiveSheet()->getColumnDimension('W')->setAutoSize(true);
		$clonedSheet1 = clone $objPHPExcel->getActiveSheet();
		// set table header : rincian
		$clonedSheet1
			->setCellValue('B10', 'NO')
			->setCellValue('C10', 'KODE LAPORAN')
			->setCellValue('D10', 'NAMA KAB/KOTA')
			->setCellValue('E10', 'NAMA DESA')
			->setCellValue('F10', 'JENIS LAPORAN')
			->setCellValue('G10', 'RINCIAN LAPORAN / KATEGORI KEGIATAN / JENIS MENTORING')
			->setCellValue('H10', 'JENIS KEGIATAN / KATEGORI ASISTENSI')
			->setCellValue('I10', 'TOPIK KEGIATAN / MEDIA MONITORING')
			->setCellValue('J10', 'SASARAN')
			->setCellValue('K10', 'Tanggal Mulai Kegiatan')
			->setCellValue('L10', 'Tanggal Selesai Kegiatan')
			->setCellValue('M10', 'Jumlah Staff Perpustakaan')
			->setCellValue('M11', 'Laki - Laki')
			->setCellValue('N11', 'Perempuan')
			->setCellValue('O10', 'Jumlah Non Staff Perpustakaan')
			->setCellValue('O11', 'Laki - Laki')
			->setCellValue('P11', 'Perempuan')
			->setCellValue('Q10', 'Jumlah Lainnya')
			->setCellValue('Q11', 'Laki - Laki')
			->setCellValue('R11', 'Perempuan')
			->setCellValue('S10', 'Total Peserta')
			->setCellValue('S11', 'Laki - Laki')
			->setCellValue('T11', 'Perempuan')
			->setCellValue('U11', 'Keseluruhan')
			->setCellValue('V10', 'Tanggal Laporan Dibuat')
			->setCellValue('W10', 'Tanggal Laporan Disetujui');
		 
		$clonedSheet1
			->mergeCells('B10:B11')->mergeCells('C10:C11')
			->mergeCells('D10:D11')->mergeCells('E10:E11')
			->mergeCells('F10:F11')->mergeCells('G10:G11')
			->mergeCells('H10:H11')->mergeCells('I10:I11')
			->mergeCells('J10:J11')
			->mergeCells('K10:K11')->mergeCells('L10:L11')
			->mergeCells('M10:N10')
			->mergeCells('O10:P10')
			->mergeCells('Q10:R10')
			->mergeCells('S10:U10')
			->mergeCells('V10:V11')->mergeCells('W10:W11')
			;
		
		
		$searchBar = array(
			'pFieldName' => 
				array("Berkas.result","Propinsi.id","Distrik.id","Desa.id","TipeBerkas.id"), 
			'pCondition' => 
				array("EQUAL",
					($this->input->post('Fpropinsi') == -1 ? "NOT_EQUAL" : "EQUAL"),
					($this->input->post('Fdistrik') == -1 ? "NOT_EQUAL" : "EQUAL"),
					($this->input->post('Fdesa') == -1 ? "NOT_EQUAL" : "EQUAL"),
					($this->input->post('FtipeBerkas') == -1 ? "NOT_EQUAL" : "EQUAL")
				), 
			'pFieldValue' => 
				array("FINISH", $this->input->post('Fpropinsi'), $this->input->post('Fdistrik'), $this->input->post('Fdesa'), $this->input->post('FtipeBerkas'))
		);

		$orders = array(
						array('fieldName' => 'Berkas.updateDate', 'orderType' => 'DESC')
					   );	
		$reports2 = $this -> em -> getRepository('\models\entities\Berkas')->paginateBerkasByMap2($searchBar, NULL, $orders);

		// set row data sheet 2
		$i1 = 1;
		$row1 = 12;
		foreach ($reports2['data'] as $value) {
			$clonedSheet1->setCellValue('B'.$row1, $i1);
			$clonedSheet1->setCellValue('C'.$row1, $value->getId());
			$clonedSheet1->setCellValue('D'.$row1, $value->getPerpustakaan()->getDesa()->getDistrik()->getNama());
			$clonedSheet1->setCellValue('E'.$row1, $value->getPerpustakaan()->getDesa()->getNama());
			
			$rincianLaporan = "";
			$jenisLaporan = $value->getTipe()->getNama();
			$topikKegiatan = "N/A";
			$jenisKegiatan = "N/A";
			$sasaran = "N/A";
			$fromDate = "";
			$toDate = "";
			$jmlhStaffP = "N/A";
			$jmlhStaffW = "N/A";
			$jmlhNonSP = "N/A";
			$jmlhNonSW = "N/A";
			$jmlhLainnya = "N/A";
			$jmlhP = "N/A";
			$jmlhW = "N/A";
			$jmlhT = "N/A";
			$jmlhLainnyaP = "N/A";
			$jmlhLainnyaW = "N/A";
			if ($value->getTipe()->getId() == "TRAINING"){
				if (strtoupper($value->getPerpustakaan()->getNama()) == "PERPUSERU" || $value->getPerpustakaan()->getId() == "P2.111"){
					$jenisLaporan = "Kegiatan Perpuseru";
				}
				$rincianLaporan = $value->getBerkasTraining()->getJenisTraining()->getNama();
				// $fromDate = $value->getBerkasTraining()->getFromDate()->format('d-m-Y');
				// $toDate = $value->getBerkasTraining()->getToDate()->format('d-m-Y');
				$fromDate = '-';
				$toDate = '-';
				if ($value->getBerkasTraining()->getFromDate() <> null) {
					$fromDate = $value->getBerkasTraining()->getFromDate()->format('d-m-Y');
				}
				
				if ($value->getBerkasTraining()->getFromDate() <> null) {
					$toDate = $value->getBerkasTraining()->getToDate()->format('d-m-Y');
				}
				
				
				$criteria = array('berkas' => $value);
				$participantOfTrainings = $this->em->getRepository('\models\entities\ParticipantOfTraining')->findBy($criteria);
				$jmlhStaffP = 0;
				$jmlhStaffW = 0;
				$jmlhNonSP = 0;
				$jmlhNonSW = 0;
				$jmlhP = 0;
				$jmlhW = 0;
				$jmlhLainnyaP = 0;
				$jmlhLainnyaW = 0;
				foreach ($participantOfTrainings as $peserta){
					
					if ($peserta->getStaff() == "S" && $peserta->getGender() == "M"){
						$jmlhStaffP++;
						$jmlhP++;
					} else if ($peserta->getStaff() == "S" && $peserta->getGender() == "F"){
						$jmlhStaffW++;
						$jmlhW++;
					} else if ($peserta->getStaff() == "N" && $peserta->getGender() == "M"){
						$jmlhNonSP++;
						$jmlhP++;
					} else if ($peserta->getStaff() == "N" && $peserta->getGender() == "F"){
						$jmlhNonSW++;
						$jmlhW++;
					} else if ($peserta->getStaff()== "L" && $peserta->getGender() == "M"){
						$jmlhLainnyaP++;
						$jmlhP++;
					} else if ($peserta->getStaff()== "L" && $peserta->getGender() == "F"){
						$jmlhLainnyaW++;
						$jmlhW++;
					}
				}
				$jmlhT = $jmlhP + $jmlhW;
			} else if ($value->getTipe()->getId() == "LKPM"){
				$jenisKegiatan = $value->getBerkasLkpm()->getJenisKegiatan()->getNama();
				$rincianLaporan = $value->getBerkasLkpm()->getJenisKegiatan()->getKategori()->getNama();
				
				// $fromDate = $value->getBerkasLkpm()->getFromDate()->format('d-m-Y');
				// $toDate = $value->getBerkasLkpm()->getToDate()->format('d-m-Y');
				
				$fromDate = '-';
				$toDate = '-';
				if ($value->getBerkasLkpm()->getFromDate() <> null) {
					$fromDate = $value->getBerkasLkpm()->getFromDate()->format('d-m-Y');
				}
				
				if ($value->getBerkasLkpm()->getFromDate() <> null) {
					$toDate = $value->getBerkasLkpm()->getToDate()->format('d-m-Y');
				}
				
				if ($value->getBerkasLkpm()->getTopikKegiatan() <> null) {
					$topikKegiatan = $value->getBerkasLkpm()->getTopikKegiatan()->getNama();
				}
				$criteria = array('berkas' => $value);
				$berkasLkpmSasarans = $this->em->getRepository('\models\entities\BerkasLkpmSasaran')->findBy($criteria);
				$arrSasarans = array();
					foreach ($berkasLkpmSasarans as $berkasLkpmSasaran){
						if ($berkasLkpmSasaran->getInstitusiLain() != null ){
							$arrSasarans[] = $berkasLkpmSasaran->getKelompokSasaran()->getNama() . "(".$berkasLkpmSasaran->getInstitusiLain().")";
						} else {
							$arrSasarans[] = $berkasLkpmSasaran->getKelompokSasaran()->getNama();
						}
					}
				$sasaran = implode(",", $arrSasarans);
				$jmlhP = $value->getBerkasLkpm()->getJumlahPria();
				$jmlhW = $value->getBerkasLkpm()->getJumlahWanita();
				$jmlhT = $jmlhP + $jmlhW;
			} else if ($value->getTipe()->getId() == "LAS"){
				//$rincianLaporan = $value->getBerkasLas()->getKategoriAsistensi()->getNama();
				// $fromDate = $value->getBerkasLas()->getFromDate()->format('d-m-Y');
				// $toDate = $value->getBerkasLas()->getToDate()->format('d-m-Y');
				if ($value->getBerkasLas()->getJenisAsistensi() == 1){
					$rincianLaporan = "NON KUNJUNGAN";
				} else {
					$rincianLaporan = "KUNJUNGAN";
				}
				
				$jenisKegiatan = $value->getBerkasLas()->getKategoriAsistensi()->getNama();
				$topikKegiatan = $value->getBerkasLas()->getMedia()->getNama();
				$fromDate = '-';
				$toDate = '-';
				if ($value->getBerkasLas()->getFromDate() <> null) {
					$fromDate = $value->getBerkasLas()->getFromDate()->format('d-m-Y');
				}
				
				if ($value->getBerkasLas()->getFromDate() <> null) {
					$toDate = $value->getBerkasLas()->getToDate()->format('d-m-Y');
				}
			}
			$clonedSheet1->setCellValue('F'.$row1, $jenisLaporan);
			$clonedSheet1->setCellValue('G'.$row1, $rincianLaporan);
			$clonedSheet1->setCellValue('H'.$row1, $jenisKegiatan); 
			$clonedSheet1->setCellValue('I'.$row1, $topikKegiatan);
			$clonedSheet1->setCellValue('J'.$row1, $sasaran);
			$clonedSheet1->setCellValue('K'.$row1, $fromDate); 
			$clonedSheet1->setCellValue('L'.$row1, $toDate); 
			$clonedSheet1->setCellValue('M'.$row1, $jmlhStaffP);
			$clonedSheet1->setCellValue('N'.$row1, $jmlhStaffW); 
			$clonedSheet1->setCellValue('O'.$row1, $jmlhNonSP);
			$clonedSheet1->setCellValue('P'.$row1, $jmlhNonSW);
			$clonedSheet1->setCellValue('Q'.$row1, $jmlhLainnyaP);
			$clonedSheet1->setCellValue('R'.$row1, $jmlhLainnyaW);
			$clonedSheet1->setCellValue('S'.$row1, $jmlhP);
			$clonedSheet1->setCellValue('T'.$row1, $jmlhW);
			$clonedSheet1->setCellValue('U'.$row1, $jmlhT);
			$clonedSheet1->setCellValue('V'.$row1, $value->getStartDate()->format('d-m-Y'));
			$clonedSheet1->setCellValue('W'.$row1, $value->getEndDate()->format('d-m-Y'));
			$i1++;
			$row1++;
		}
		
		$clonedSheet1->setSharedStyle($border, "B10:W" . (10+$i1));
		$clonedSheet1->setTitle('Rincian');
		$objPHPExcel->addSheet($clonedSheet1);
		
		//$clonedSheet2 = clone $objPHPExcel->getActiveSheet();
		$clonedSheet2 = $objPHPExcel->createSheet();
		$clonedSheet2->setCellValue('A1', "REKAPITULASI LAPORAN FINAL - PROPINSI " . $propinsi)
					->setCellValue('A2', ' ')
					->setCellValue('A3', 'Propinsi : ' . $propinsi)
		            ->setCellValue('A4', 'Kab/Kota : ' . $distrik)
		            ->setCellValue('A5', 'Desa : ' . $desa)
		            ->setCellValue('A6', 'Jenis Laporan : ' . $tipe_berkas)
		            ->setCellValue('A7', 'Rincian Laporan : ' . $rincian_laporan)
		            ->setCellValue('A8', 'Tanggal Export : ' . date("dmY H:i:s"));
		// set table header : namaPeserta
		$clonedSheet2
			->setCellValue('B10', 'KODE LAPORAN')
			->setCellValue('C10', 'NAMA PESERTA')
			->setCellValue('D10', 'JENIS KELAMIN')
			->setCellValue('E10', 'STAF/NON STAF/LAINNYA');
			// ->setCellValue('F10', 'RINCIAN LAPORAN')
			// ->setCellValue('G10', 'JUMLAH LAPORAN');
		
		$clonedSheet2->getColumnDimension('B')->setAutoSize(true);
		$clonedSheet2->getColumnDimension('C')->setAutoSize(true);
		$clonedSheet2->getColumnDimension('D')->setAutoSize(true);
		$clonedSheet2->getColumnDimension('E')->setAutoSize(true);		
		// set row data sheet 3
		$i1 = 1;
		$row1 = 11;
		foreach ($reports2['data'] as $value) {
			if ($value->getTipe()->getId()=="TRAINING"){
				$criteria = array('berkas' => $value);
			    $participantOfTrainings = $this->em->getRepository('\models\entities\ParticipantOfTraining')->findBy($criteria);
				foreach ($participantOfTrainings as $peserta){
					$clonedSheet2->setCellValue('B'.$row1, $value->getId());
					$clonedSheet2->setCellValue('C'.$row1, $peserta->getName());
					$gender = ($peserta->getGender() == 'M' ? 'Laki-Laki' : 'Perempuan');
					$staff = "Lainnya";
					if ($peserta->getStaff() == 'S'){
						$staff = "Staff";
					} else if ($peserta->getStaff() == 'N'){
						$staff = "Non Staff";
					}
					$clonedSheet2->setCellValue('D'.$row1, $gender);
					$clonedSheet2->setCellValue('E'.$row1, $staff);
					$i1++;
					$row1++;
				}		
			}
		}
		
		$clonedSheet2->setSharedStyle($border, "B10:E" . (9+$i1));
		$clonedSheet2->setTitle('Nama Peserta Pelatihan');
		//$objPHPExcel->addSheet($clonedSheet2);
		
		$objPHPExcel->setActiveSheetIndex(0);
		
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
		//die();
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		//header('Content-Disposition: attachment;filename="'."rekapitulasi_" . date("Y_m_d") . '.xls'.'"');
		header('Content-Disposition: attachment;filename="'."rekapitulasi_" . date("Y_m_d").".xlsx");
		header('Cache-Control: max-age=0');
		// If you're serving to IE 9, then the following may be needed
		header('Cache-Control: max-age=1');
		
		// If you're serving to IE over SSL, then the following may be needed
		header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
		header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
		header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
		header ('Pragma: public'); // HTTP/1.0
		
		$objWriter->save('php://output');
	}
	
}

